// 数据持久层；引入 sql
const { query } = require("../../../mysql");

//联合查询文件夹和文件数据sql
exports.findFolderAndfileMap = async (userid, folderid, state = 1) =>
  folderid
    ? await query(
        `select * from (SELECT fileid as id,filename as name,filetype as type,filesuffix as ext,owner,ownerName,state,fileownerfolderid as parentId,currenthead,createtime FROM files WHERE OWNER ='${userid}' AND fileownerfolderid='${folderid}' AND state='${state}' UNION ALL SELECT folderid as id,foldername as name,null as type,null as ext,owner,ownerName,state,parentfolderid as parentId,null as currenthead,createtime  FROM folders WHERE OWNER ='${userid}' AND state='${state}' AND parentfolderid='${folderid}') t1  ORDER BY t1.createtime DESC`
      )
    : await query(
        `select * from (SELECT fileid as id,filename as name,filetype as type,filesuffix as ext,owner,ownerName,state,fileownerfolderid as parentId,currenthead,createtime FROM files WHERE OWNER ='${userid}' AND ISNULL(fileownerfolderid) AND state='${state}' UNION ALL SELECT folderid as id,foldername as name,null as type,null as ext,owner,ownerName,state,parentfolderid as parentId,null as currenthead,createtime FROM folders WHERE OWNER ='${userid}' AND state='${state}' AND ISNULL(parentfolderid)) t1 ORDER BY t1.createtime DESC`
      );

//联合查询文件夹和文件数据是否存在
exports.findFilesOrFoldersMap = async (id) =>
  await query(
    `select * from (SELECT fileid as id,filename as name,filetype as type,filesuffix as ext,owner,state,fileownerfolderid as parentId,currenthead,createtime FROM files  UNION ALL SELECT folderid as id,foldername as name,null as type,null as ext,owner,state,parentfolderid as parentId,null as currenthead,createtime FROM folders ) t1 WHERE t1.id='${id}'`
  );
